using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using Nop.Core;
using Nop.Core.Domain.Catalog;
using Nop.Core.Domain.Common;
using Nop.Services.Catalog;
using Nop.Services.Directory;
using Nop.Services.Media;
using Nop.Services.Seo;
using OfficeOpenXml;

namespace Nop.Services.ExportImport
{
    /// <summary>
    /// Import manager
    /// </summary>
    public partial class ImportManager : IImportManager
    {
        private readonly IProductService _productService;
        private readonly ICategoryService _categoryService;
        private readonly IManufacturerService _manufacturerService;
        private readonly IPictureService _pictureService;
        private readonly IUrlRecordService _urlRecordService;
        private readonly ICountryService _countryService;
        private readonly IStateProvinceService _stateProvinceService;

        public ImportManager(IProductService productService, ICategoryService categoryService,
            IManufacturerService manufacturerService, IPictureService pictureService,
            IUrlRecordService urlRecordService,
            ICountryService countryService, IStateProvinceService stateProvinceService)
        {
            this._productService = productService;
            this._categoryService = categoryService;
            this._manufacturerService = manufacturerService;
            this._pictureService = pictureService;
            this._urlRecordService = urlRecordService;
            this._countryService = countryService;
            this._stateProvinceService = stateProvinceService;
        }

        protected virtual int GetColumnIndex(string[] properties, params string[] columnNames)
        {
            if (properties == null)
                throw new ArgumentNullException("properties");

            if (columnNames == null)
                throw new ArgumentNullException("columnNames");

            for (int i = 0; i < properties.Length; i++)
            {
                foreach (var c in columnNames)
                {
                    if (properties[i].Equals(c.Trim(), StringComparison.InvariantCultureIgnoreCase))
                        return i + 1; //excel indexes start from 1
                }
            }
            return 0;
        }

        protected virtual T GetCellValue<T>(string[] properties, ExcelRange cells, int row, params string[] columnNames)
        {
            var col = GetColumnIndex(properties, columnNames);
            if (col == 0)
            {
                return default(T);
            }

            return cells[row, col].GetValue<T>();
        }

        /// <summary>
        /// Import customer address from XLSX file
        /// </summary>
        /// <param name="stream"></param>
        public virtual IList<Address> ImportAddressesFromXlsx(Stream stream)
        {
            if (stream == null)
                throw new ArgumentNullException("stream");

            using (var xlPackage = new ExcelPackage(stream))
            {
                // get the first worksheet in the workbook
                var worksheet = xlPackage.Workbook.Worksheets.FirstOrDefault();
                if (worksheet == null)
                    throw new NopException("No worksheet found");

                //the columns
                //TODO: Check address setting for each importing fields

                int iProp = 1;
                var props = new List<string>();

                while (true)
                {
                    var c = worksheet.Cells[1, iProp++].Value;
                    if (c == null || String.IsNullOrEmpty(c.ToString()))
                    {
                        break;
                    }
                    props.Add(c.ToString());
                }
                var properties = props.ToArray();

                int iRow = 2;
                var addresses = new List<Address>();
                while (true)
                {
                    bool allColumnsAreEmpty = true;
                    for (var i = 1; i <= properties.Length; i++)
                        if (worksheet.Cells[iRow, i].Value != null && !String.IsNullOrEmpty(worksheet.Cells[iRow, i].Value.ToString()))
                        {
                            allColumnsAreEmpty = false;
                            break;
                        }
                    if (allColumnsAreEmpty)
                        break;

                    string company = GetCellValue<string>(properties, worksheet.Cells, iRow, "Company");
                    string firstName = GetCellValue<string>(properties, worksheet.Cells, iRow, "FirstName", "First Name", "Name");
                    if (String.IsNullOrEmpty(firstName))
                    {
                        throw new NopException("The first name is required. Row: {0}", iRow);
                    }

                    string lastName = GetCellValue<string>(properties, worksheet.Cells, iRow, "LastName", "Last Name");
                    string address1 = GetCellValue<string>(properties, worksheet.Cells, iRow, "Address1", "Address");
                    if (String.IsNullOrEmpty(address1))
                    {
                        throw new NopException("The address is required. Row: {0}", iRow);
                    }

                    string address2 = GetCellValue<string>(properties, worksheet.Cells, iRow, "Address2");
                    string city = GetCellValue<string>(properties, worksheet.Cells, iRow, "City");
                    if (String.IsNullOrEmpty(city))
                    {
                        throw new NopException("The city is required. Row: {0}", iRow);
                    }
                    string zipPostalCode = GetCellValue<string>(properties, worksheet.Cells, iRow, "ZipPostalCode", "Postal Code", "Zip Code");
                    if (String.IsNullOrEmpty(zipPostalCode))
                    {
                        throw new NopException("The zip/postal code is required. Row: {0}", iRow);
                    }

                    string country = GetCellValue<string>(properties, worksheet.Cells, iRow, "Country");
                    int countryId = 0;
                    if (!String.IsNullOrEmpty(country))
                    {
                        country = country.Trim();
                        var c = _countryService.GetAllCountries().SingleOrDefault(x =>
                            x.Name.Equals(country, StringComparison.InvariantCultureIgnoreCase) ||
                            x.TwoLetterIsoCode.Equals(country, StringComparison.InvariantCultureIgnoreCase) ||
                            x.ThreeLetterIsoCode.Equals(country, StringComparison.InvariantCultureIgnoreCase));
                        countryId = c == null ? 0 : c.Id;
                    }
                    else
                    {
                        countryId = _countryService.GetAllCountries().First().Id;
                    }

                    int stateProvinceId = 0;
                    string stateProvince = GetCellValue<string>(properties, worksheet.Cells, iRow, "StateProvince", "State", "Province");
                    if (String.IsNullOrEmpty(stateProvince))
                    {
                        throw new NopException("The state/province is required. Row: {0}", iRow);
                    }

                    if (countryId > 0)
                    {
                        if (!String.IsNullOrEmpty(stateProvince))
                        {
                            stateProvince = stateProvince.Trim();
                            var s = _stateProvinceService.GetStateProvincesByCountryId(countryId).SingleOrDefault(x =>
                                x.Name.Equals(stateProvince, StringComparison.InvariantCultureIgnoreCase) ||
                                x.Abbreviation.Equals(stateProvince, StringComparison.InvariantCultureIgnoreCase));

                            if (s == null)
                            {
                                throw new NopException("The state/province '{0}' is invalid. Row: {1}", stateProvince, iRow);
                            }
                            stateProvinceId = s == null ? 0 : s.Id;
                        }
                    }

                    string cellphone = GetCellValue<string>(properties, worksheet.Cells, iRow, "Cellphone");
                    if (String.IsNullOrEmpty(cellphone))
                    {
                        throw new NopException("The cellphone is required. Row: {0}", iRow);
                    }

                    string phone = GetCellValue<string>(properties, worksheet.Cells, iRow, "Phone");
                    string fax = GetCellValue<string>(properties, worksheet.Cells, iRow, "Fax");
                    string email = GetCellValue<string>(properties, worksheet.Cells, iRow, "Email");
                    string type = GetCellValue<string>(properties, worksheet.Cells, iRow, "Type");
                    AddressType addrType = AddressType.Unknown;
                    if (!String.IsNullOrEmpty(type) && !Enum.TryParse<AddressType>(type, out addrType))
                    {
                        throw new NopException("The address type '{0}' is invalid. Row: {1}", type, iRow);
                    }

                    addresses.Add(new Address
                        {
                            FirstName = firstName,
                            LastName = lastName,
                            Company = company,
                            Address1 = address1,
                            Address2 = address2,
                            CellPhoneNumber = cellphone,
                            PhoneNumber = phone,
                            City = city,
                            CountryId = countryId,
                            Email = email,
                            FaxNumber = fax,
                            StateProvinceId = stateProvinceId,
                            Type = addrType,
                            ZipPostalCode = zipPostalCode

                        });
                    //next address
                    iRow++;
                }
                return addresses;
            }
        }

        /// <summary>
        /// Import products from XLSX file
        /// </summary>
        /// <param name="stream">Stream</param>
        public virtual void ImportProductsFromXlsx(Stream stream)
        {
            // ok, we can run the real code of the sample now
            using (var xlPackage = new ExcelPackage(stream))
            {
                // get the first worksheet in the workbook
                var worksheet = xlPackage.Workbook.Worksheets.FirstOrDefault();
                if (worksheet == null)
                    throw new NopException("No worksheet found");

                //the columns
                var properties = new string[]
                {
                    "Store",
                    "Name",
                    "ShortDescription",
                    "FullDescription",
                    "ProductTemplateId",
                    "ShowOnHomePage",
                    "MetaKeywords",
                    "MetaDescription",
                    "MetaTitle",
                    "SeName",
                    "AllowCustomerReviews",
                    "Published",
                    "ProductVariantName",
                    "SKU",
                    "ManufacturerPartNumber",
                    "Gtin",
                    "IsGiftCard",
                    "GiftCardTypeId",
                    "RequireOtherProducts",
                    "RequiredProductVariantIds",
                    "AutomaticallyAddRequiredProductVariants",
                    "IsDownload",
                    "DownloadId",
                    "UnlimitedDownloads",
                    "MaxNumberOfDownloads",
                    "DownloadActivationTypeId",
                    "HasSampleDownload",
                    "SampleDownloadId",
                    "HasUserAgreement",
                    "UserAgreementText",
                    "IsRecurring",
                    "RecurringCycleLength",
                    "RecurringCyclePeriodId",
                    "RecurringTotalCycles",
                    "IsShipEnabled",
                    "IsFreeShipping",
                    "AdditionalShippingCharge",
                    "IsTaxExempt",
                    "TaxCategoryId",
                    "ManageInventoryMethodId",
                    "StockQuantity",
                    "DisplayStockAvailability",
                    "DisplayStockQuantity",
                    "MinStockQuantity",
                    "LowStockActivityId",
                    "NotifyAdminForQuantityBelow",
                    "BackorderModeId",
                    "AllowBackInStockSubscriptions",
                    "OrderMinimumQuantity",
                    "OrderMaximumQuantity",
                    "AllowedQuantities",
                    "DisableBuyButton",
                    "DisableWishlistButton",
                    "CallForPrice",
                    "Price",
                    "OldPrice",
                    "ProductCost",
                    "SpecialPrice",
                    "SpecialPriceStartDateTimeUtc",
                    "SpecialPriceEndDateTimeUtc",
                    "CustomerEntersPrice",
                    "MinimumCustomerEnteredPrice",
                    "MaximumCustomerEnteredPrice",
                    "Weight",
                    "Length",
                    "Width",
                    "Height",
                    "CreatedOnUtc",
                    "CategoryIds",
                    "ManufacturerIds",
                    "Picture1",
                    "Picture2",
                    "Picture3",
                    "Picture4",
                    "Picture5",
                };


                int iRow = 2;
                while (true)
                {
                    bool allColumnsAreEmpty = true;
                    for (var i = 1; i <= properties.Length; i++)
                        if (worksheet.Cells[iRow, i].Value != null && !String.IsNullOrEmpty(worksheet.Cells[iRow, i].Value.ToString()))
                        {
                            allColumnsAreEmpty = false;
                            break;
                        }
                    if (allColumnsAreEmpty)
                        break;

                    int storeId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "Store")].Value);
                    string name = worksheet.Cells[iRow, GetColumnIndex(properties, "Name")].Value as string;
                    string shortDescription = worksheet.Cells[iRow, GetColumnIndex(properties, "ShortDescription")].Value as string;
                    string fullDescription = worksheet.Cells[iRow, GetColumnIndex(properties, "FullDescription")].Value as string;
                    int productTemplateId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ProductTemplateId")].Value);
                    bool showOnHomePage = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "ShowOnHomePage")].Value);
                    string metaKeywords = worksheet.Cells[iRow, GetColumnIndex(properties, "MetaKeywords")].Value as string;
                    string metaDescription = worksheet.Cells[iRow, GetColumnIndex(properties, "MetaDescription")].Value as string;
                    string metaTitle = worksheet.Cells[iRow, GetColumnIndex(properties, "MetaTitle")].Value as string;
                    string seName = worksheet.Cells[iRow, GetColumnIndex(properties, "SeName")].Value as string;
                    bool allowCustomerReviews = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "AllowCustomerReviews")].Value);
                    bool published = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "Published")].Value);
                    string productVariantName = worksheet.Cells[iRow, GetColumnIndex(properties, "ProductVariantName")].Value as string;
                    string sku = worksheet.Cells[iRow, GetColumnIndex(properties, "SKU")].Value as string;
                    string manufacturerPartNumber = worksheet.Cells[iRow, GetColumnIndex(properties, "ManufacturerPartNumber")].Value as string;
                    string gtin = worksheet.Cells[iRow, GetColumnIndex(properties, "Gtin")].Value as string;
                    bool isGiftCard = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsGiftCard")].Value);
                    int giftCardTypeId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "GiftCardTypeId")].Value);
                    bool requireOtherProducts = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "RequireOtherProducts")].Value);
                    string requiredProductVariantIds = worksheet.Cells[iRow, GetColumnIndex(properties, "RequiredProductVariantIds")].Value as string;
                    bool automaticallyAddRequiredProductVariants = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "AutomaticallyAddRequiredProductVariants")].Value);
                    bool isDownload = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsDownload")].Value);
                    int downloadId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "DownloadId")].Value);
                    bool unlimitedDownloads = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "UnlimitedDownloads")].Value);
                    int maxNumberOfDownloads = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "MaxNumberOfDownloads")].Value);
                    int downloadActivationTypeId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "DownloadActivationTypeId")].Value);
                    bool hasSampleDownload = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "HasSampleDownload")].Value);
                    int sampleDownloadId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "SampleDownloadId")].Value);
                    bool hasUserAgreement = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "HasUserAgreement")].Value);
                    string userAgreementText = worksheet.Cells[iRow, GetColumnIndex(properties, "UserAgreementText")].Value as string;
                    bool isRecurring = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsRecurring")].Value);
                    int recurringCycleLength = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "RecurringCycleLength")].Value);
                    int recurringCyclePeriodId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "RecurringCyclePeriodId")].Value);
                    int recurringTotalCycles = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "RecurringTotalCycles")].Value);
                    bool isShipEnabled = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsShipEnabled")].Value);
                    bool isFreeShipping = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsFreeShipping")].Value);
                    decimal additionalShippingCharge = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "AdditionalShippingCharge")].Value);
                    bool isTaxExempt = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsTaxExempt")].Value);
                    int taxCategoryId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "TaxCategoryId")].Value);
                    int manageInventoryMethodId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ManageInventoryMethodId")].Value);
                    int stockQuantity = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "StockQuantity")].Value);
                    bool displayStockAvailability = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "DisplayStockAvailability")].Value);
                    bool displayStockQuantity = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "DisplayStockQuantity")].Value);
                    int minStockQuantity = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "MinStockQuantity")].Value);
                    int lowStockActivityId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "LowStockActivityId")].Value);
                    int notifyAdminForQuantityBelow = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "NotifyAdminForQuantityBelow")].Value);
                    int backorderModeId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "BackorderModeId")].Value);
                    bool allowBackInStockSubscriptions = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "AllowBackInStockSubscriptions")].Value);
                    int orderMinimumQuantity = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "OrderMinimumQuantity")].Value);
                    int orderMaximumQuantity = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "OrderMaximumQuantity")].Value);
                    string allowedQuantities = worksheet.Cells[iRow, GetColumnIndex(properties, "AllowedQuantities")].Value as string;
                    bool disableBuyButton = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "DisableBuyButton")].Value);
                    bool disableWishlistButton = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "DisableWishlistButton")].Value);
                    bool callForPrice = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "CallForPrice")].Value);
                    decimal price = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Price")].Value);
                    decimal oldPrice = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "OldPrice")].Value);
                    decimal productCost = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "ProductCost")].Value);
                    decimal? specialPrice = null;
                    var specialPriceExcel = worksheet.Cells[iRow, GetColumnIndex(properties, "SpecialPrice")].Value;
                    if (specialPriceExcel != null)
                        specialPrice = Convert.ToDecimal(specialPriceExcel);
                    DateTime? specialPriceStartDateTimeUtc = null;
                    var specialPriceStartDateTimeUtcExcel = worksheet.Cells[iRow, GetColumnIndex(properties, "SpecialPriceStartDateTimeUtc")].Value;
                    if (specialPriceStartDateTimeUtcExcel != null)
                        specialPriceStartDateTimeUtc = DateTime.FromOADate(Convert.ToDouble(specialPriceStartDateTimeUtcExcel));
                    DateTime? specialPriceEndDateTimeUtc = null;
                    var specialPriceEndDateTimeUtcExcel = worksheet.Cells[iRow, GetColumnIndex(properties, "SpecialPriceEndDateTimeUtc")].Value;
                    if (specialPriceEndDateTimeUtcExcel != null)
                        specialPriceEndDateTimeUtc = DateTime.FromOADate(Convert.ToDouble(specialPriceEndDateTimeUtcExcel));

                    bool customerEntersPrice = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "CustomerEntersPrice")].Value);
                    decimal minimumCustomerEnteredPrice = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "MinimumCustomerEnteredPrice")].Value);
                    decimal maximumCustomerEnteredPrice = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "MaximumCustomerEnteredPrice")].Value);
                    decimal weight = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Weight")].Value);
                    decimal length = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Length")].Value);
                    decimal width = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Width")].Value);
                    decimal height = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Height")].Value);
                    DateTime createdOnUtc = DateTime.FromOADate(Convert.ToDouble(worksheet.Cells[iRow, GetColumnIndex(properties, "CreatedOnUtc")].Value));
                    string categoryIds = worksheet.Cells[iRow, GetColumnIndex(properties, "CategoryIds")].Value as string;
                    string manufacturerIds = worksheet.Cells[iRow, GetColumnIndex(properties, "ManufacturerIds")].Value as string;
                    string picture1 = worksheet.Cells[iRow, GetColumnIndex(properties, "Picture1")].Value as string;
                    string picture2 = worksheet.Cells[iRow, GetColumnIndex(properties, "Picture2")].Value as string;
                    string picture3 = worksheet.Cells[iRow, GetColumnIndex(properties, "Picture3")].Value as string;
                    string picture4 = worksheet.Cells[iRow, GetColumnIndex(properties, "Picture4")].Value as string;
                    string picture5 = worksheet.Cells[iRow, GetColumnIndex(properties, "Picture5")].Value as string;

                    // NOTES: (rputih)
                    // When importing, try find if product variant already exists for that store.
                    // IF yes, the just update that product variant (and related product) record.
                    // IF not, then create a new product variant (and related product) record.
                    // 
                    // Existing (and the best) way of finding existing product variant is via SKU.
                    // However, SKU can be null in the product variant table.
                    //
                    // So, as a backup, added a second way of finding existing product variant via
                    // (required) Product's name AND (optional) Product variant's name.
                    // A product variant is existing if it has the same product name and, if non-empty,
                    // the same product variant name -- not including leading & trailing spaces.
                    // When a product is created, the default variant normally has an "empty" name.
                    // Thus, the variant name is optional. In fact, multiple variants of the same 
                    // product can have empty names or even same names. In this case, one of them
                    // will be simply selected as the "existing" product variant.
                    //
                    // IT IS BEST if we always name a product variant, and name them all uniquely!

                    // Same SKU can exist in more than one store, I believe.
                    var productVariant = _productService.GetProductVariantBySku(sku, storeId);

                    if (productVariant != null)
                    {
                        //product
                        var product = productVariant.Product;
                        // product.StoreId = storeId;   // Should not need to re-set this!
                        product.Name = name;
                        product.ShortDescription = shortDescription;
                        product.FullDescription = fullDescription;
                        product.ProductTemplateId = productTemplateId;
                        product.ShowOnHomePage = showOnHomePage;
                        product.MetaKeywords = metaKeywords;
                        product.MetaDescription = metaDescription;
                        product.MetaTitle = metaTitle;
                        product.AllowCustomerReviews = allowCustomerReviews;
                        product.Published = published;
                        product.CreatedOnUtc = createdOnUtc;
                        product.UpdatedOnUtc = DateTime.UtcNow;
                        _productService.UpdateProduct(product);

                        //search engine name
                        _urlRecordService.SaveSlug(product, product.ValidateSeName(seName, product.Name, true), 0);

                        //variant
                        productVariant.Name = productVariantName;
                        productVariant.Sku = sku;
                        productVariant.ManufacturerPartNumber = manufacturerPartNumber;
                        productVariant.Gtin = gtin;
                        productVariant.IsGiftCard = isGiftCard;
                        productVariant.GiftCardTypeId = giftCardTypeId;
                        productVariant.RequireOtherProducts = requireOtherProducts;
                        productVariant.RequiredProductVariantIds = requiredProductVariantIds;
                        productVariant.AutomaticallyAddRequiredProductVariants = automaticallyAddRequiredProductVariants;
                        productVariant.IsDownload = isDownload;
                        productVariant.DownloadId = downloadId;
                        productVariant.UnlimitedDownloads = unlimitedDownloads;
                        productVariant.MaxNumberOfDownloads = maxNumberOfDownloads;
                        productVariant.DownloadActivationTypeId = downloadActivationTypeId;
                        productVariant.HasSampleDownload = hasSampleDownload;
                        productVariant.SampleDownloadId = sampleDownloadId;
                        productVariant.HasUserAgreement = hasUserAgreement;
                        productVariant.UserAgreementText = userAgreementText;
                        productVariant.IsRecurring = isRecurring;
                        productVariant.RecurringCycleLength = recurringCycleLength;
                        productVariant.RecurringCyclePeriodId = recurringCyclePeriodId;
                        productVariant.RecurringTotalCycles = recurringTotalCycles;
                        productVariant.IsShipEnabled = isShipEnabled;
                        productVariant.IsFreeShipping = isFreeShipping;
                        productVariant.AdditionalShippingCharge = additionalShippingCharge;
                        productVariant.IsTaxExempt = isTaxExempt;
                        productVariant.TaxCategoryId = taxCategoryId;
                        productVariant.ManageInventoryMethodId = manageInventoryMethodId;
                        productVariant.StockQuantity = stockQuantity;
                        productVariant.DisplayStockAvailability = displayStockAvailability;
                        productVariant.DisplayStockQuantity = displayStockQuantity;
                        productVariant.MinStockQuantity = minStockQuantity;
                        productVariant.LowStockActivityId = lowStockActivityId;
                        productVariant.NotifyAdminForQuantityBelow = notifyAdminForQuantityBelow;
                        productVariant.BackorderModeId = backorderModeId;
                        productVariant.AllowBackInStockSubscriptions = allowBackInStockSubscriptions;
                        productVariant.OrderMinimumQuantity = orderMinimumQuantity;
                        productVariant.OrderMaximumQuantity = orderMaximumQuantity;
                        productVariant.AllowedQuantities = allowedQuantities;
                        productVariant.DisableBuyButton = disableBuyButton;
                        productVariant.DisableWishlistButton = disableWishlistButton;
                        productVariant.CallForPrice = callForPrice;
                        productVariant.Price = price;
                        productVariant.OldPrice = oldPrice;
                        productVariant.ProductCost = productCost;
                        productVariant.SpecialPrice = specialPrice;
                        productVariant.SpecialPriceStartDateTimeUtc = specialPriceStartDateTimeUtc;
                        productVariant.SpecialPriceEndDateTimeUtc = specialPriceEndDateTimeUtc;
                        productVariant.CustomerEntersPrice = customerEntersPrice;
                        productVariant.MinimumCustomerEnteredPrice = minimumCustomerEnteredPrice;
                        productVariant.MaximumCustomerEnteredPrice = maximumCustomerEnteredPrice;
                        productVariant.Weight = weight;
                        productVariant.Length = length;
                        productVariant.Width = width;
                        productVariant.Height = height;
                        productVariant.Published = published;
                        productVariant.CreatedOnUtc = createdOnUtc;
                        productVariant.UpdatedOnUtc = DateTime.UtcNow;

                        _productService.UpdateProductVariant(productVariant);
                    }
                    else
                    {
                        var product = _productService.GetProductByName(name, storeId);

                        if (product == null)
                        {
                            //product
                            product = new Product()
                            {
                                StoreId = storeId,
                                Name = name,
                                ShortDescription = shortDescription,
                                FullDescription = fullDescription,
                                ShowOnHomePage = showOnHomePage,
                                MetaKeywords = metaKeywords,
                                MetaDescription = metaDescription,
                                MetaTitle = metaTitle,
                                AllowCustomerReviews = allowCustomerReviews,
                                Published = published,
                                CreatedOnUtc = createdOnUtc,
                                UpdatedOnUtc = DateTime.UtcNow
                            };
                            _productService.InsertProduct(product);
                        }
                        else
                        {
                            product.ShortDescription = shortDescription;
                            product.FullDescription = fullDescription;
                            product.ShowOnHomePage = showOnHomePage;
                            product.MetaKeywords = metaKeywords;
                            product.MetaDescription = metaDescription;
                            product.MetaTitle = metaTitle;
                            product.AllowCustomerReviews = allowCustomerReviews;
                            product.Published = published;
                            product.CreatedOnUtc = createdOnUtc;
                            product.UpdatedOnUtc = DateTime.UtcNow;

                            _productService.UpdateProduct(product);
                        }

                        //search engine name
                        _urlRecordService.SaveSlug(product, product.ValidateSeName(seName, product.Name, true), 0);

                        //vairant
                        productVariant = product.ProductVariants.Where(pv => pv.Name.Trim().ToLowerInvariant().Equals(productVariantName.Trim().ToLowerInvariant())).FirstOrDefault();

                        if (productVariant == null)
                        {
                            productVariant = new ProductVariant()
                            {
                                ProductId = product.Id,
                                Name = productVariantName,
                                Sku = sku,
                                ManufacturerPartNumber = manufacturerPartNumber,
                                Gtin = gtin,
                                IsGiftCard = isGiftCard,
                                GiftCardTypeId = giftCardTypeId,
                                RequireOtherProducts = requireOtherProducts,
                                RequiredProductVariantIds = requiredProductVariantIds,
                                AutomaticallyAddRequiredProductVariants = automaticallyAddRequiredProductVariants,
                                IsDownload = isDownload,
                                DownloadId = downloadId,
                                UnlimitedDownloads = unlimitedDownloads,
                                MaxNumberOfDownloads = maxNumberOfDownloads,
                                DownloadActivationTypeId = downloadActivationTypeId,
                                HasSampleDownload = hasSampleDownload,
                                SampleDownloadId = sampleDownloadId,
                                HasUserAgreement = hasUserAgreement,
                                UserAgreementText = userAgreementText,
                                IsRecurring = isRecurring,
                                RecurringCycleLength = recurringCycleLength,
                                RecurringCyclePeriodId = recurringCyclePeriodId,
                                RecurringTotalCycles = recurringTotalCycles,
                                IsShipEnabled = isShipEnabled,
                                IsFreeShipping = isFreeShipping,
                                AdditionalShippingCharge = additionalShippingCharge,
                                IsTaxExempt = isTaxExempt,
                                TaxCategoryId = taxCategoryId,
                                ManageInventoryMethodId = manageInventoryMethodId,
                                StockQuantity = stockQuantity,
                                DisplayStockAvailability = displayStockAvailability,
                                DisplayStockQuantity = displayStockQuantity,
                                MinStockQuantity = minStockQuantity,
                                LowStockActivityId = lowStockActivityId,
                                NotifyAdminForQuantityBelow = notifyAdminForQuantityBelow,
                                BackorderModeId = backorderModeId,
                                AllowBackInStockSubscriptions = allowBackInStockSubscriptions,
                                OrderMinimumQuantity = orderMinimumQuantity,
                                OrderMaximumQuantity = orderMaximumQuantity,
                                AllowedQuantities = allowedQuantities,
                                DisableBuyButton = disableBuyButton,
                                CallForPrice = callForPrice,
                                Price = price,
                                OldPrice = oldPrice,
                                ProductCost = productCost,
                                SpecialPrice = specialPrice,
                                SpecialPriceStartDateTimeUtc = specialPriceStartDateTimeUtc,
                                SpecialPriceEndDateTimeUtc = specialPriceEndDateTimeUtc,
                                CustomerEntersPrice = customerEntersPrice,
                                MinimumCustomerEnteredPrice = minimumCustomerEnteredPrice,
                                MaximumCustomerEnteredPrice = maximumCustomerEnteredPrice,
                                Weight = weight,
                                Length = length,
                                Width = width,
                                Height = height,
                                Published = published,
                                CreatedOnUtc = createdOnUtc,
                                UpdatedOnUtc = DateTime.UtcNow
                            };

                            _productService.InsertProductVariant(productVariant);
                        }
                        else
                        {
                            productVariant.Sku = sku;
                            productVariant.ManufacturerPartNumber = manufacturerPartNumber;
                            productVariant.Gtin = gtin;
                            productVariant.IsGiftCard = isGiftCard;
                            productVariant.GiftCardTypeId = giftCardTypeId;
                            productVariant.RequireOtherProducts = requireOtherProducts;
                            productVariant.RequiredProductVariantIds = requiredProductVariantIds;
                            productVariant.AutomaticallyAddRequiredProductVariants = automaticallyAddRequiredProductVariants;
                            productVariant.IsDownload = isDownload;
                            productVariant.DownloadId = downloadId;
                            productVariant.UnlimitedDownloads = unlimitedDownloads;
                            productVariant.MaxNumberOfDownloads = maxNumberOfDownloads;
                            productVariant.DownloadActivationTypeId = downloadActivationTypeId;
                            productVariant.HasSampleDownload = hasSampleDownload;
                            productVariant.SampleDownloadId = sampleDownloadId;
                            productVariant.HasUserAgreement = hasUserAgreement;
                            productVariant.UserAgreementText = userAgreementText;
                            productVariant.IsRecurring = isRecurring;
                            productVariant.RecurringCycleLength = recurringCycleLength;
                            productVariant.RecurringCyclePeriodId = recurringCyclePeriodId;
                            productVariant.RecurringTotalCycles = recurringTotalCycles;
                            productVariant.IsShipEnabled = isShipEnabled;
                            productVariant.IsFreeShipping = isFreeShipping;
                            productVariant.AdditionalShippingCharge = additionalShippingCharge;
                            productVariant.IsTaxExempt = isTaxExempt;
                            productVariant.TaxCategoryId = taxCategoryId;
                            productVariant.ManageInventoryMethodId = manageInventoryMethodId;
                            productVariant.StockQuantity = stockQuantity;
                            productVariant.DisplayStockAvailability = displayStockAvailability;
                            productVariant.DisplayStockQuantity = displayStockQuantity;
                            productVariant.MinStockQuantity = minStockQuantity;
                            productVariant.LowStockActivityId = lowStockActivityId;
                            productVariant.NotifyAdminForQuantityBelow = notifyAdminForQuantityBelow;
                            productVariant.BackorderModeId = backorderModeId;
                            productVariant.AllowBackInStockSubscriptions = allowBackInStockSubscriptions;
                            productVariant.OrderMinimumQuantity = orderMinimumQuantity;
                            productVariant.OrderMaximumQuantity = orderMaximumQuantity;
                            productVariant.AllowedQuantities = allowedQuantities;
                            productVariant.DisableBuyButton = disableBuyButton;
                            productVariant.CallForPrice = callForPrice;
                            productVariant.Price = price;
                            productVariant.OldPrice = oldPrice;
                            productVariant.ProductCost = productCost;
                            productVariant.SpecialPrice = specialPrice;
                            productVariant.SpecialPriceStartDateTimeUtc = specialPriceStartDateTimeUtc;
                            productVariant.SpecialPriceEndDateTimeUtc = specialPriceEndDateTimeUtc;
                            productVariant.CustomerEntersPrice = customerEntersPrice;
                            productVariant.MinimumCustomerEnteredPrice = minimumCustomerEnteredPrice;
                            productVariant.MaximumCustomerEnteredPrice = maximumCustomerEnteredPrice;
                            productVariant.Weight = weight;
                            productVariant.Length = length;
                            productVariant.Width = width;
                            productVariant.Height = height;
                            productVariant.Published = published;
                            productVariant.CreatedOnUtc = createdOnUtc;
                            productVariant.UpdatedOnUtc = DateTime.UtcNow;

                            _productService.UpdateProductVariant(productVariant);
                        }
                    }

                    //category mappings
                    if (!String.IsNullOrEmpty(categoryIds))
                    {
                        foreach (var id in categoryIds.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries).Select(x => Convert.ToInt32(x.Trim())))
                        {
                            if (productVariant.Product.ProductCategories.Where(x => x.CategoryId == id).FirstOrDefault() == null)
                            {
                                //ensure that category exists
                                var category = _categoryService.GetCategoryById(id);
                                if (category != null)
                                {
                                    var productCategory = new ProductCategory()
                                    {
                                        ProductId = productVariant.Product.Id,
                                        CategoryId = category.Id,
                                        IsFeaturedProduct = false,
                                        DisplayOrder = 1
                                    };
                                    _categoryService.InsertProductCategory(productCategory);
                                }
                            }
                        }
                    }

                    //manufacturer mappings
                    if (!String.IsNullOrEmpty(manufacturerIds))
                    {
                        foreach (var id in manufacturerIds.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries).Select(x => Convert.ToInt32(x.Trim())))
                        {
                            if (productVariant.Product.ProductManufacturers.Where(x => x.ManufacturerId == id).FirstOrDefault() == null)
                            {
                                //ensure that manufacturer exists
                                var manufacturer = _manufacturerService.GetManufacturerById(id);
                                if (manufacturer != null)
                                {
                                    var productManufacturer = new ProductManufacturer()
                                    {
                                        ProductId = productVariant.Product.Id,
                                        ManufacturerId = manufacturer.Id,
                                        IsFeaturedProduct = false,
                                        DisplayOrder = 1
                                    };
                                    _manufacturerService.InsertProductManufacturer(productManufacturer);
                                }
                            }
                        }
                    }

                    // replace binaries of existing product pictures, and then either
                    // create new ones (as required), or delete remaining existing pictures (if these are not being replaced).

                    //pictures
                    int numExistingProductPictures = productVariant.Product.ProductPictures.Count();
                    int productPictureIdx = 0;
                    foreach (var picture in new string[] { picture1, picture2, picture3, picture4, picture5 })
                    {
                        if (String.IsNullOrEmpty(picture))
                            continue;

                        if (productPictureIdx < numExistingProductPictures)
                        {
                            // case when we want to simply replace the picture & related info for existing picture table record
                            int picId = productVariant.Product.ProductPictures.ElementAt(productPictureIdx).PictureId;
                            _pictureService.UpdatePicture(picId, File.ReadAllBytes(picture), "image/jpeg", _pictureService.GetPictureSeName(name), true);
                        }
                        else
                        {
                            // case when need to create new record in the picture table
                            productVariant.Product.ProductPictures.Add(new ProductPicture()
                            {
                                Picture = _pictureService.InsertPicture(File.ReadAllBytes(picture), "image/jpeg", _pictureService.GetPictureSeName(name), true),
                                DisplayOrder = 1,
                            });

                            _productService.UpdateProduct(productVariant.Product);
                        }

                        productPictureIdx++;
                    }

                    for (int ppIdx = numExistingProductPictures - 1; ppIdx >= productPictureIdx; ppIdx--)
                    {
                        var productPicture = productVariant.Product.ProductPictures.ElementAt(ppIdx);
                        _productService.DeleteProductPicture(productPicture);

                        var picture = _pictureService.GetPictureById(productPicture.PictureId);
                        _pictureService.DeletePicture(picture);

                        productVariant.Product.ProductPictures.Remove(productPicture);
                        _productService.UpdateProduct(productVariant.Product);
                    }

                    //update "HasTierPrices" and "HasDiscountsApplied" properties
                    _productService.UpdateHasTierPricesProperty(productVariant);
                    _productService.UpdateHasDiscountsApplied(productVariant);



                    //next product
                    iRow++;
                }
            }
        }
    }
}
